﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace SisCRA.Database
{
    public class DisciplineDAL
    {
        #region -Constants-

        private const string TABLE = "DISCIPLINA";
        private const string INSERT_DISCIPLINE = "INSERT INTO T_DISCIPLINA (DC_CODIGO, DC_NOME, DN_MAX_FALTAS) VALUES (@CODIGO, @NOME, @MAX_FALTAS)";
        private const string UPDATE_DISCIPLINE = "UPDATE T_DISCIPLINA SET DC_CODIGO = @CODIGO, DC_NOME = @NOME, DN_MAX_FALTAS = @MAX_FALTAS WHERE PK_DISCIPLINA_ID = @ID";
        private const string GET_DISCIPLINE_BY_ID = "SELECT PK_DISCIPLINA_ID AS ID, DC_CODIGO AS CODIGO, DC_NOME AS NOME, DN_MAX_FALTAS AS MAXIMO_FALTAS_PERMITIDAS FROM T_DISCIPLINA WHERE PK_DISCIPLINA_ID = @ID";
        private const string GET_DISCIPLINE_BY_CODE = "SELECT PK_DISCIPLINA_ID AS ID, DC_CODIGO AS CODIGO, DC_NOME AS NOME, DN_MAX_FALTAS AS MAXIMO_FALTAS_PERMITIDAS FROM T_DISCIPLINA WHERE DC_CODIGO = @CODIGO";
        private const string GET_DISCIPLINE_BY_NAME = "SELECT PK_DISCIPLINA_ID AS ID, DC_CODIGO AS CODIGO, DC_NOME AS NOME, DN_MAX_FALTAS AS MAXIMO_FALTAS_PERMITIDAS FROM T_DISCIPLINA WHERE DC_NOME LIKE @NOME";
        private const string GET_ALL = "SELECT PK_DISCIPLINA_ID AS ID, DC_CODIGO AS CODIGO, DC_NOME AS NOME, DN_MAX_FALTAS AS MAXIMO_FALTAS_PERMITIDAS FROM T_DISCIPLINA";

        #endregion

        #region -Private Attributes-

        MySqlConnection conn;
        MySqlCommand cmd;
        MySqlDataAdapter da;

        #endregion

        #region -Public Methods-

        /// <summary>
        /// Método que insere uma disciplina no banco de dados.
        /// </summary>
        /// <param name="code">Código da disciplina</param>
        /// <param name="name">Nome da disciplina</param>
        /// <param name="maxFaults">Número máximo de faltas permitidas</param>
        public void InsertDiscipline(string code, string name, int maxFaults)
        {
            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(INSERT_DISCIPLINE, conn);
                cmd.Parameters.AddWithValue("@CODIGO", code);
                cmd.Parameters.AddWithValue("@NOME", name);
                cmd.Parameters.AddWithValue("@MAX_FALTAS", maxFaults);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que atualiza uma disciplina no banco de dados.
        /// </summary>
        /// <param name="id">Id da disciplina</param>
        /// <param name="code">Código da disciplina</param>
        /// <param name="name">Nome da disciplina</param>
        /// <param name="maxFaults">Número máximo de faltas permitidas</param>
        public void UpdateDiscipline(int id, string code, string name, int maxFaults)
        {
            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(UPDATE_DISCIPLINE, conn);
                cmd.Parameters.AddWithValue("@CODIGO", code);
                cmd.Parameters.AddWithValue("@NOME", name);
                cmd.Parameters.AddWithValue("@MAX_FALTAS", maxFaults);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca uma disciplina pelo Id.
        /// </summary>
        /// <param name="id">Id da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetDisciplineById(int id)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_DISCIPLINE_BY_ID, conn);
                cmd.Parameters.AddWithValue("@ID", id);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca uma disciplina pelo Código.
        /// </summary>
        /// <param name="code">Código da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetDisciplineByCode(string code)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_DISCIPLINE_BY_CODE, conn);
                cmd.Parameters.AddWithValue("@CODIGO", code);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca uma disciplina pelo Nome.
        /// </summary>
        /// <param name="name">Nome da disciplina</param>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetDisciplineByName(string name)
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_DISCIPLINE_BY_NAME, conn);
                cmd.Parameters.AddWithValue("@NOME", "%" + name + "%");
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        /// <summary>
        /// Método que busca todas as disciplinas.
        /// </summary>
        /// <returns>DataTable contendo a busca realizada</returns>
        public DataTable GetAll()
        {
            DataTable dt = new DataTable(TABLE);

            try
            {
                conn = Connection.Instance.Conn;
                conn.Open();
                cmd = new MySqlCommand(GET_ALL, conn);
                da = new MySqlDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(dt);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
        }

        #endregion
    }
}
